USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetJobTitlesXml')
	DROP FUNCTION dbo.GetJobTitlesXml
GO


CREATE FUNCTION [dbo].[GetJobTitlesXml] () 
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result = '<JOBTITLES><entry id="0" type=""></entry>'

	select @result = @result + '<entry id="' + CAST([JobTitle] AS VARCHAR) + '"><![CDATA[' +  JobTitleDesc + ']]></entry>'
	FROM TR_JOBTITLE 
	Order by JobTitleDesc
	
	SET @result = @result + '</JOBTITLES>'

	return @result

END


-- SELECT dbo.GetJobTitlesXml()